import copy
import datetime
import math
import os
import sys
import time
from itertools import groupby
from operator import itemgetter
import numpy as np

from src.utils import CommonUtils

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger

# 区域指标分位值计算
class RegionCalcIndicatorQuantile:
    def __init__(self, logger, localDb, dataCenterDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb
        self.size = 1000

    def __loadProcessList(self):
        sql = """
        SELECT  year  from index_process_year
            where TYPE =0 order by YEAR  desc
        """
        result = list(self.localDb.Query(sql))
        result = [int(item['year']) for item in result]
        return result

    # 抽取待更新主体名单

    def __loadRegionIndexList__(self, year, code):
        sql = """
            SELECT
            a.id,
            a.region_code,
            a.`year`,
            a.index_code,
            a.index_value,
            b.province_code,
            b.city_code ,
            b.county_code,
            b.functional_code, 
            b.`level` 
        FROM
            region_index a
            left join regions b on a.region_code =b.code 
        where
            a.year = %s
            and a.index_code = %s and a.region_code != '100000' and b.release_status =1 and b.ver =0
        """
        return self.localDb.Query(sql, (year, code))

    def __loadSettings__(self):
        sql = " SELECT *  from region_index_calc_settings"
        return self.localDb.Query(sql)

    def __insertResult(self, year, index_code, result):
        sql = """
            INSERT INTO region_index_quantile
            (id,`year`, index_code, min, tf, fi, sf, `type`, `max`,region_code)
            VALUES(null, %s, %s, %s, %s, %s, %s, %s, %s,%s);
        """

        delSql = """
            DELETE FROM region_index_quantile
            WHERE year=%s and index_code =%s
        """
        self.localDb.ExecNoneQuery(delSql, (year, index_code))
        self.localDb.ExecMany(sql, result)

    def quantileCal(self, year, indexCode, data, type, regionCode):
        result = []
        result.append(year)
        result.append(indexCode)
        tupleIndustryData = list({CommonUtils.strToNum(dd["index_value"]) for dd in list(data)})
        tupleIndustryData = list(filter(None, tupleIndustryData))
        if len(tupleIndustryData) == 0:
            result.append(None)
            result.append(None)
            result.append(None)
            result.append(None)
            result.append(type)
            result.append(None)
            result.append(None if regionCode is None else regionCode)
        else:
            result.append(np.min(tupleIndustryData))
            result.append(np.quantile(tupleIndustryData, .25))
            result.append(np.quantile(tupleIndustryData, .50))
            result.append(np.quantile(tupleIndustryData, .75))
            result.append(type)
            result.append(np.max(tupleIndustryData))
            result.append(None if regionCode is None else regionCode)
        print(type, "====", result)
        return result

    # 计算全国(全国所有省)
    def nationwide(self, finList: list, year, indexCode, type):
        result = []
        data = list(filter(lambda x: x["level"] == 1, finList))
        result.append(self.quantileCal(year, indexCode, data, type, None))
        return result

    # 每个省下面所有区县分位值
    def countyByProvince(self, finList: list, year, indexCode, type):
        result = []
        data = list(filter(lambda x: x["level"] == 3, finList))
        data.sort(key=itemgetter("province_code"))
        for provinceCode, d in groupby(data, key=itemgetter('province_code')):
            result.append(self.quantileCal(year, indexCode, list(d), type, provinceCode))
        return result
        # 计算每个省所有地市

    def cityByProvince(self, finList: list, year, indexCode, type):
        result = []
        data = list(filter(lambda x: x["level"] == 2, finList))
        data.sort(key=itemgetter("province_code"))
        for provinceCode, d in groupby(data, key=itemgetter('province_code')):
            result.append(self.quantileCal(year, indexCode, list(d), type, provinceCode))
        return result


    # 计算全国所有区县分位值
    def countyByNationwide(self, finList: list, year, indexCode, type):
        result = []
        data = list(filter(lambda x: x["level"] == 3, finList))
        result.append(self.quantileCal(year, indexCode, data, type, None))
        return result

    # 计算全市（全国所有市）
    def cityByNationwide(self, finList: list, year, indexCode, type):
        result = []
        data = list(filter(lambda x: x["level"] == 2, finList))
        result.append(self.quantileCal(year, indexCode, data, type, None))
        return result

    def run(self):
        # 加载设置
        settings = self.__loadSettings__()
        years = self.__loadProcessList()

        for year in years:
            for setting in settings:
                result = []
                code = setting["code"]
                finList = self.__loadRegionIndexList__(year, code)

                # 计算每个省下面所有区县分位值
                result = result + self.countyByProvince(finList, year, code, 3)

                # 计算每个省所有地市
                result = result + self.cityByProvince(finList, year, code, 2)

                # 计算全国
                result = result + self.nationwide(finList, year, code, 0)

                # 计算全国所有市分位值
                result = result + self.cityByNationwide(finList, year, code, 1)

                # 计算全国所有区县分位值
                result = result + self.countyByNationwide(finList, year, code, 4)
                self.__insertResult(year, code, result)


def __main__():
    logger = Logger()

    t = time.perf_counter()
    logger.info("指标计算 0.0.1")

    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    cleanScreen()

    sync = RegionCalcIndicatorQuantile(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb, fcdb=fcdb)
    sync.run()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('指标计算结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))


# 程序入口
if __name__ == "__main__":
    __main__()
